package com.sikaryofficial.backend.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.nacos.shaded.com.google.gson.Gson;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.sikaryofficial.backend.domain.entity.ProductQr;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.compress.utils.Lists;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author : qinjinyuan
 * @desc :
 * @date : 2023/11/09 19:08
 */
@NoArgsConstructor
@Data
@Slf4j
public class JsonFileRead {
    static Map<Long,String> idAndMd5Map = new HashMap<>();
    static {
        // 产品ID和MD5
        idAndMd5Map.put(1739158028694908941L,"0759b08e454d48799dcdd405ef237367");
        idAndMd5Map.put(1747581915820580867L,"36e53c920c17fcef7622ec09297d6334");
        idAndMd5Map.put(1747581915820580868L,"88e905cd07efcc323bca3d8faecbee19");
        idAndMd5Map.put(1747581915820580872L,"c9263da24eb7569ad6bdf72aa63bd290");
        idAndMd5Map.put(1747581915820580874L,"2c7a548fab29bae79b04c95589b0b11b");
        idAndMd5Map.put(1747581915820580875L,"55cdb0d2356ada00cce653377aa489ba");
        idAndMd5Map.put(1747581915820580878L,"701e7173409a92026fbca0d2d45de73b");
        idAndMd5Map.put(1747581915820580881L,"4a73f2b90ae9b23cc23160619a017a78");
        idAndMd5Map.put(1747581915820580883L,"4a5729ac37ffb11712ede2577189cdb7");
        idAndMd5Map.put(1747581915820580884L,"c1a983230c080a1a047d6b10a4ba1820");
        idAndMd5Map.put(1747581915820580886L,"33e79d47b80514859337fd49776c9406");
    }


    public static void main(String[] args) {
        // genCountryGeoData();
        // getCity("E:\\行政区划信息\\gadm41_USA_2.json");

        getQrcodeScanData("E:\\tmp\\qrcodeScanData.json");

        // printProductQrCodeSQL();
      //  createUserBalanceData();


        // printSQLForCountry();



//        String url ="https://ske.hismk1.com?p=1840a87e4c61e003_1866c_tlab&e=1";
//        url = url.substring(url.indexOf("=")+1);
//        String param = url.split("&")[0];
//        System.out.println(param);
    }

    private static void printSQLForCountry() {

        String source = "共和国刚果 | COG | 共和国刚果;哥伦比亚 | COL | 哥伦比亚;韩国 | KOR | 韩国;科威特 | KWT | 科威特;土耳其 | TUR | 土耳其;阿鲁巴 | ABW | 阿鲁巴;奥兰群岛 | ALA | 奥兰群岛;美属萨摩亚 | ASM | 美属萨摩亚;贝里斯 | BLZ | 贝里斯;哥斯达黎加 | CRI | 哥斯达黎加;开普 verde | CPV | 开普 verde;福克兰群岛（马尔维纳斯群岛）| FLK | 福克兰群岛（马尔维纳斯群岛）;法罗群岛 | FRO | 法罗群岛;法属圭亚那 | GUF | 法属圭亚那;法属波利尼西亚 | PYF | 法属波利尼西亚;法属南部领地 | ATF | 法属南部领地;格陵兰 | GRL | 格陵兰;直布罗陀 | GIB | 直布罗陀;关岛 | GUM | 关岛;根西岛 | GGY | 根西岛;香港 | HKG | 香港;曼岛 | IMN | 曼岛;澳门 | MAC | 澳门;摩纳哥 | MCO | 摩纳哥;蒙特塞拉特 | MSR | 蒙特塞拉特;努伊 | NIU | 努伊;诺福克岛 | NFK | 诺福克岛;北马里亚纳群岛 | MNP | 北马里亚纳群岛;皮特凯恩 | PCN | 皮特凯恩;波多黎各 | PRI | 波多黎各;巴勒斯坦 | PSE | 巴勒斯坦;留尼汪 | REU | 留尼汪;圣赫勒拿 | SHN | 圣赫勒拿;圣皮埃尔和密克隆 | SPM | 圣皮埃尔和密克隆;斯瓦尔巴群岛和扬马延 | SJM | 斯瓦尔巴群岛和扬马延;土库曼斯坦 | TKM | 土库曼斯坦;特克斯和凯科斯群岛 | TCA | 特克斯和凯科斯群岛;图瓦卢 | TUV | 图瓦卢;瓦努阿图 | VUT | 瓦努阿图;梵蒂冈城国（圣座）| VAT | 梵蒂冈城国（圣座）;英属维尔京群岛 | VGB | 英属维尔京群岛;美属维尔京群岛 | VIR | 美属维尔京群岛;沃利斯和富图纳 | WLF | 沃利斯和富图纳;西撒哈拉 | ESH | 西撒哈拉;也门 | YEM | 也门;安圭拉 | AIA | 安圭拉;南极洲 | ATA | 南极洲;百慕大 | BMU | 百慕大;英属印度洋领地 | IOT | 英属印度洋领地;开曼群岛 | CYM | 开曼群岛;通道群岛 | CYN | 通道群岛;库克群岛 | COK | 库克群岛;库拉索 | CUW | 库拉索;赫德岛和麦克唐纳群岛 | HMD | 赫德岛和麦克唐纳群岛;泽西岛 | JER | 泽西岛;基里巴斯 | KIR | 基里巴斯;新喀里多尼亚 | NCL | 新喀里多尼亚;帕劳 | PLW | 帕劳;圣巴泰勒米 | BLM | 圣巴泰勒米;圣马丁（法国部分）| MAF | 圣马丁（法国部分）;萨摩亚 | WSM | 萨摩亚;圣马丁（荷兰部分）| SXM | 圣马丁（荷兰部分）;台湾 | TWN | 台湾;特立尼达和多巴哥 | TTO | 特立尼达和多巴哥;联合国 | UNO | 联合国;美国本土外小岛屿 | UMI | 美国本土外小岛屿";

        String[] arr = source.split(";");
        for (String s : arr) {
            String[] itemArr = s.split("\\|");
            StringBuilder sb = new StringBuilder(
            );
// UPDATE `hismk_cloud`.`country_t` SET `country_name` = 'United Arab Emirates', `iso_country_code` = 'ARE', `country_short_code` = 'AE',
// // `country_name_zh` = NULL, `has_union` = 0 WHERE `country_id` = 1816637114155945986;

          sb.append("UPDATE `country_t` SET  `country_name_zh` = '"+itemArr[0].trim()+"' where iso_country_code='" + itemArr[1].trim() + "';" );
           System.out.println(sb);
        }
    }



    /**
     * 生成二维码表的SQL
     */
    private static void printProductQrCodeSQL() {
        String path = "E:\\销量前10\\测试\\";
        File file = new File(path);
        if(file.isDirectory()) {
            File[] files = file.listFiles((dir, name) -> name.endsWith(".xlsx"));
            System.out.println(files.length);
            for(File excel : files){
                ExcelReader excelReader = ExcelUtil.getReader(excel);
                List<Map<String, Object>> allDataList = excelReader.readAll();
                int rowIndex = 1;
                List<String> sqlList = Lists.newArrayList();
                for (Map<String, Object> data : allDataList) {
                    StringBuilder sb = new StringBuilder();
                    // 遍历Excel数据到对应 productImportVO 的字段
                    String url = MapUtils.getString(data,"二维码地址");
                    Long seqNo = MapUtils.getLong(data,"序号");
                    url = url.substring(url.indexOf("=")+1);
                    String param = url.split("&")[0];
                    String[] paramArr =param.split(ProductUtil.PARAM_SPLIT_KEY);
                    String pIdStr =paramArr[0];
                    String seqNoStr = paramArr[1];
                    System.out.println(seqNo+" ------>"+ProductUtil.getIdByHexStr(seqNoStr));
                    Long brandTasteId = ProductUtil.getIdByHexStr(pIdStr);
                    ProductQr productQr = new ProductQr();
                    productQr.setProductQrId(IdWorker.getId());
                    productQr.setSeqNo(seqNo);
                    productQr.setParam(param);
                    productQr.setBrandTasteId(brandTasteId);

                    sb.append("INSERT INTO product_qr_t(product_qr_id, brand_taste_id, seq_no, created_by, created_time, param) VALUES (" +
                            productQr.getProductQrId()+ " , " +
                            productQr.getBrandTasteId() + " , " +
                            productQr.getSeqNo() + ", " +
                            " -1, now(), " +
                            "'" +
                            productQr.getParam() +
                            "');"
                    );
                    System.out.println(sb);
                    sqlList.add(sb.toString());
                    rowIndex++;
                }
                writeDataToTxtFile( sqlList,file, excel.getName().replace("xlsx","sql"));
            }
        }
    }

    /**
     * 生成二维码表的SQL
     */
    private static void createUserBalanceData() {
        String path = "E:\\销量前10\\用户余额\\";
        File file = new File(path);
        if(file.isDirectory()) {
            File[] files = file.listFiles((dir, name) -> name.endsWith(".xlsx"));
            System.out.println(files.length);
            for(File excel : files){
                ExcelReader excelReader = ExcelUtil.getReader(excel);
                List<Map<String, Object>> allDataList = excelReader.readAll();
                int rowIndex = 1;
                List<String> sqlList = Lists.newArrayList();
                for (Map<String, Object> data : allDataList) {
                    StringBuilder sb = new StringBuilder();
                    // 遍历Excel数据到对应 productImportVO 的字段
                    Long userId = MapUtils.getLong(data,"用户ID");
                    sb.append("INSERT INTO points_balance_t (points_id, balance, tenant_id,  created_by, created_time) VALUES (" +
                            IdWorker.getId()+ " , " +
                            20000 + " , " +
                            1 + ", " +
                            userId + " , " +
                            "now()); "
                    );
                    System.out.println(sb);
                    sqlList.add(sb.toString());
                    rowIndex++;
                }
                writeDataToTxtFile( sqlList,file, excel.getName().replace("xlsx","sql"));
            }
        }
    }

    /**
     * 批量加工并生成SQL数据文件
     */
    private static void genCountryGeoData() {
        String path = "E:\\国家多边形数据\\";
        File file = new File(path);
        System.out.println(file.isDirectory());
        AtomicInteger count = new AtomicInteger();
        if(file.isDirectory()){
            File[]  files =  file.listFiles((dir, name) -> name.endsWith(".json"));
            System.out.println(files.length);

            String finalPath = path;

            Arrays.stream(files).sorted(Comparator.comparing(File::getName)).forEach(item->{
                System.out.println(item.getName());
                String fileName = item.getName();
                String targetFileName = fileName.replace("json","sql");
                count.set(count.get() + getCountryByFeatures(finalPath, fileName, targetFileName));
            });
        }
        System.out.println(count.get());
    }

    private static void getQrcodeScanData(String fileName) {
        try {
            String content = new String(Files.readAllBytes(Paths.get(fileName)));
            JSONArray features  = JSON.parseArray(content);
            for(int i=0;i<features.size();i++){
                JSONObject citem = JSON.parseObject(features.get(i).toString());
                // System.out.println("总数量："+citem.get("scanCodeNumSum") + "____" + citem.get("brand_taste_id"));
                StringBuilder sb = new StringBuilder();
//                sb.append("INSERT INTO `product_statistics_t`(`product_statistics_id`, `brand_taste_id`, `created_by`, `created_time` ,`normal_scan_count`) " +
//                        "VALUES (" +  IdWorker.getId() + "," + citem.get("brand_taste_id") + ", 0, now()," + citem.get("scanCodeNumSum") + ");" );
//
           // UPDATE `product_statistics_t` SET `updated_by` = 0, `updated_time` = now(),`normal_scan_count` = 6 WHERE `brand_taste_id` = 1772181486252904476;
                sb.append("UPDATE `product_statistics_t` SET `updated_by` = 0, `updated_time` = now(),`normal_scan_count` = " + citem.get("scanCodeNumSum") + " WHERE `brand_taste_id` = " + citem.get("brand_taste_id") + ";" );
               System.out.println(sb);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    private static void getCity(String fileName) {
        try {
            String content = new String(Files.readAllBytes(Paths.get(fileName)));
            JSONObject jsonObject = JSON.parseObject(content);

            JSONArray features  = JSON.parseArray(jsonObject.get("features").toString());
            int count = 0;
            Set<String> type2Set = new TreeSet<>();
            for(int i=0;i<features.size();i++){
                JSONObject citem = JSON.parseObject(features.get(i).toString());
                JSONObject  properties = JSON.parseObject(citem.get("properties").toString());
                System.out.println(properties.get("NAME_2"));
                if("California".equals(properties.get("NAME_1").toString())){
                    type2Set.add(properties.get("NAME_2").toString());
                }
//

//                System.out.println( "COUNTRY:" +properties.get("COUNTRY")  + ",area:"+ properties.get("NAME_1") + ",city:" + properties.get("NAME_2")
//                        + ",area3:"+ properties.get("NAME_3") + ",city4:" + properties.get("NAME_4"));
//               if(CharSequenceUtil.equals("IsleofAnglesey",properties.get("NAME_2").toString())){
//                   System.out.println(",city:" + properties.get("NAME_2") + ",NAME_3："+ properties.get("NAME_3").toString() + ",NAME_4:" + properties.get("NAME_4").toString());
//                   count++;
//               }
            }
            System.out.println("总数量："+count);
            System.out.println("总数量："+type2Set);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 国家多边形数据
     */
    private static int getCountryByFeatures(String path,String fileName,String targetFileName) {
        List<String> sqlList = Lists.newArrayList();
        try {
            String content = new String(Files.readAllBytes(Paths.get(path.concat(fileName))));
            JSONObject jsonObject = JSON.parseObject(content);
            JSONArray clist  = JSON.parseArray(jsonObject.get("features").toString());
            // 取第0个节点
            JSONObject clistOne = JSON.parseObject(clist.get(0).toString());
            JSONObject geometry = JSON.parseObject(clistOne.get("geometry").toString());
            JSONObject properties = JSON.parseObject(clistOne.get("properties").toString());
            String country = properties.get("COUNTRY").toString();
            String countryShortName = properties.get("GID_0").toString();
            JSONArray coordinates = JSON.parseArray(geometry.get("coordinates").toString());
            for (int i=0;i<coordinates.size();i++){
                JSONArray coordinateItemFirst = JSON.parseArray(coordinates.get(i).toString());
                for (int j=0;j<coordinateItemFirst.size();j++){
                    JSONArray coordinateItemSecond= JSON.parseArray(coordinateItemFirst.get(j).toString());
                    StringBuilder item = new StringBuilder();
                    StringBuilder sb = new StringBuilder(
                            "INSERT INTO gadm_country_geo_t(country_geo_id, country, iso_country_code, created_by, created_time, area_geo) VALUES (" +
                                    IdWorker.getId() + " , " +
                                    "'" + country + "', " +
                                    "'" + countryShortName + "', " +
                                    "0, now() , " +
                                    "ST_GeomFromText('POLYGON(("
                    );
                    List<String> llList = new ArrayList<>();
                    for (int m=0; m<coordinateItemSecond.size();m++){
                        String latAndLon = coordinateItemSecond.get(m).toString().replaceAll("\\[", "").replaceAll("]", "");
                        String[] latAndLonArray = latAndLon.split(",");
                        String lon = latAndLonArray[1];
                        String lat = latAndLonArray[0];
                        llList.add(lon + " " + lat );
                    }
                    item.append(CollUtil.join(llList, ", "));
                    sb.append(item);
                    sb.append( "))', 4326));");
                    sqlList.add(sb.toString());
                }
            }
            // writeDataToTxtFile(sqlList, new File(path.concat("SQL\\")), targetFileName);
            System.out.println(sqlList.size());
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sqlList.size();
    }


    private static void writeDataToTxtFile(List<String> sqlList, File folder, String fileNameTarget) {
        // 写入数据到Excel
        String fileName = fileNameTarget;
        String filePath = folder.getPath() + File.separator + fileName;
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            for (String sql : sqlList) {
                writer.write(sql + "\n");
            }
        } catch (IOException e) {
            log.error("writeDataToTxtFile error{}",e);
        }
    }


    private static void getGisDataByFeatures() {
        try {
            String content = new String(Files.readAllBytes(Paths.get("E:\\gadm41_GBR_4.json")));
            JSONObject jsonObject = JSON.parseObject(content);

            JSONArray clist  = JSON.parseArray(jsonObject.get("features").toString());
            for(int i=0;i<clist.size();i++) {
                JSONObject citem = JSON.parseObject(clist.get(i).toString());
                JSONObject properties = JSON.parseObject(citem.get("properties").toString());
                System.out.println(properties);
                MyAreaDTO areaDTO = JSON.parseObject(citem.get("properties").toString(), MyAreaDTO.class);
               // System.out.println(areaDTO);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    private static void getCity2() {
        Gson gson = new Gson();

        try (FileReader reader = new FileReader("E:\\response.json")) {
            Map<String, Object> jsonMap = gson.fromJson(reader, Map.class);
            System.out.println(jsonMap.get("英国"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



}


